﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;using System.Configuration;
using System.Windows.Forms;

namespace ChairtyBank.DAL
{
    class CustomerEntity
    {
        public Int32 Id { get; set; }

        public String Name { get; set; }
        public DateTime DateOfBirth { get; set; }
        public Int32 Age
        {
            get
            {
                return DateTime.Now.Year - DateOfBirth.Year;
            }
      
        }



        /*
 
            Class Client which contains all methods related to Customer table
 
        */

    }


    class Customer
    {
        //Set absolute DB path rather than relative to solve openFileDialog reset path problem
        private static string conString = ConfigurationManager.ConnectionStrings["ChairtyBank"].ToString();
        private static string cmdText = "";





        public static CustomerEntity GetClient(int id)
        {
            CustomerEntity c = null;


            cmdText = "select * from customer where customer_id=@customer_id";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@customer_id", id);
            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    c = fillEntityFromReader(reader);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return c;
        }


        /*   
           public static CustomerEntity GetClient(String pid)
           {
             CustomerEntity c = null;


               cmdText = "select * from Customer where customer_id=?";
               SqlConnection con = new SqlConnection(conString);
               SqlCommand cmd = new SqlCommand(cmdText, con);
               cmd.Parameters.AddWithValue("@customer_id", pid);
               try
               {
                   con.Open();

                   SqlDataReader reader = cmd.ExecuteReader();
                   if(reader.Read())
                   {
                       c = fillEntityFromReader(reader);
                   }
                
               }
               catch (Exception ex)
               {
                   MessageBox.Show("Exception==>>" + ex.Message);
               }

               finally
               {
                   if(con.State == ConnectionState.Open)
                       con.Close();
               }

               return c;

           }

   */

        public static List<CustomerEntity> GetAllClients()
        {
            CustomerEntity c = null;
            List<CustomerEntity> clients = new List<CustomerEntity>();

            cmdText = "select * from Customer ";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);

            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    c = fillEntityFromReader(reader);
                    clients.Add(c);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return clients;

        }

       
        
        public static bool Add(CustomerEntity c)
        {
            bool flag = true;
            cmdText = "insert into Customer (customer_id,customer_name,DOB)values(@customer_id,@customer_name,@DOB)";

            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            
            cmd.Parameters.AddWithValue("@customer_id", c.Id);
            cmd.Parameters.AddWithValue("@customer_name", c.Name);
            cmd.Parameters.AddWithValue("@DOB", c.DateOfBirth);


            //cmd.Parameters.AddWithValue("@Customer_Address", c.Address);
            //cmd.Parameters.AddWithValue("@Customer_Phone", c.Phone);
            //cmd.Parameters.AddWithValue("@Customer_Job", c.Job);
            //cmd.Parameters.AddWithValue("@Akhesaye", c.Akhesaye);
            //cmd.Parameters.AddWithValue("@Round", c.Round);

            try
            {
                con.Open();
                Int32 numOfRow = cmd.ExecuteNonQuery();
                if (numOfRow == 0)
                {
                    flag = false;
                }
                else {
                    MessageBox.Show(numOfRow + "Record added");
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى البيان كود العميل ربما يكون مكرراً" + ex.Message);
                flag = false;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return flag;

        }

        

        public static bool Update(CustomerEntity c)
        {
            Boolean flag = false;
            cmdText = "UPDATE customer SET customer_name=@customer_name ,DOB=@Customer_DOB WHERE customer_id=@customer_id";

            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@customer_name", c.Name);
            cmd.Parameters.AddWithValue("@Customer_DOB", c.DateOfBirth);
            cmd.Parameters.AddWithValue("@customer_id", c.Id);


            //cmd.Parameters.AddWithValue("@Customer_Address", c.Address);
            //cmd.Parameters.AddWithValue("@Customer_Phone", c.Phone);
            //cmd.Parameters.AddWithValue("@Customer_Job", c.Job);
            //cmd.Parameters.AddWithValue("@Akhesaye", c.Akhesaye);
            //cmd.Parameters.AddWithValue("@Round", c.Round);
            

            try
            {
                con.Open();

                Int32 numOfRow = cmd.ExecuteNonQuery();
                MessageBox.Show(numOfRow + "Record updated");
                flag = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return flag;
        }

                /*
           Delete using id or Serial 
        */

        public static bool Delete(Int32 c)
        {
            Boolean flag = false;
            cmdText = "Delete from customer WHERE customer_id=@custome_Id";


            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@custome_Id", c);
            try
            {
                con.Open();

                Int32 numOfRow = cmd.ExecuteNonQuery();
                MessageBox.Show(numOfRow + "Record Deleted");
                flag = true;


            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();

            }
            return flag;
        }



        public static List<CustomerEntity> GetClients(string name)
        {
            CustomerEntity c = null;
            List<CustomerEntity> clients = new List<CustomerEntity>();

            cmdText = " select * from customer where customer_name LIKE @customer_name ";

            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@customer_name", name);
            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    c = fillEntityFromReader(reader);
                    clients.Add(c);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return clients;
        }


        private static CustomerEntity fillEntityFromReader(SqlDataReader reader)
        {
            CustomerEntity c = new CustomerEntity();
            c.Id = Int32.Parse(reader["customer_id"].ToString());
            c.Name = reader["customer_name"].ToString();
            if (reader["DOB"] != null)
                c.DateOfBirth = (DateTime)reader["DOB"];

            //if (reader["Customer_Address"] != null)
              //  c.Address = reader["Customer_Address"].ToString();
            //if (reader["Customer_Phone"] != null)
              //  c.Phone = reader["Customer_Phone"].ToString();
            //if (reader["Customer_Job"] != null)
              //  c.Job = reader["Customer_Job"].ToString();
            
            //if (reader["Akhesaye"] != null)
              //  c.Akhesaye = reader["Akhesaye"].ToString();
            //if (reader["Round"] != null)
              //  c.Round = Int32.Parse(reader["Round"].ToString());

            return c;
        }


    }
}